package com.sharedmobility.dao;

import com.sharedmobility.model.Vehicle;
import com.sharedmobility.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.math.BigDecimal;
import java.nio.ByteBuffer;
import java.sql.Types;

public class VehicleDAO {
    private Connection getConnection() throws SQLException {
        return DBUtil.getConnection();
    }

    public List<Vehicle> findAll(String type, String sortBy) throws SQLException {
        StringBuilder sql = new StringBuilder(
            "SELECT id, type, brand, model, plate_number, price_per_minute, " +
            "ST_X(location) as longitude, ST_Y(location) as latitude, " +
            "CONCAT('POINT(', ST_X(location), ' ', ST_Y(location), ')') as location_str, " +
            "status FROM vehicles WHERE 1=1");
        
        if (type != null && !type.isEmpty()) {
            sql.append(" AND type = ?");
        }
        
        if (sortBy != null && !sortBy.isEmpty()) {
            sql.append(" ORDER BY ").append(sortBy);
        }
        
        List<Vehicle> vehicles = new ArrayList<>();
        
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {
            
            if (type != null && !type.isEmpty()) {
                stmt.setString(1, type);
            }
            
            // 只在调试模式下输出SQL
            if (System.getProperty("debug") != null) {
                System.out.println("Executing SQL: " + sql.toString());
            }
            
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                vehicles.add(mapVehicle(rs));
            }
        }
        
        return vehicles;
    }

    public Vehicle findById(Integer id) throws SQLException {
        String sql = "SELECT id, type, brand, model, plate_number, price_per_minute, " +
                    "ST_X(location) as longitude, ST_Y(location) as latitude, " +
                    "CONCAT('POINT(', ST_X(location), ' ', ST_Y(location), ')') as location_str, " +
                    "status FROM vehicles WHERE id = ?";
        
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, id);
            
            // 只在调试模式下输出SQL
            if (System.getProperty("debug") != null) {
                System.out.println("Executing findById SQL: " + sql);
            }
            
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapVehicle(rs);
            }
        }
        
        return null;
    }

    private Vehicle mapVehicle(ResultSet rs) throws SQLException {
        Vehicle vehicle = new Vehicle();
        try {
            vehicle.setId(rs.getInt("id"));
            vehicle.setType(rs.getString("type"));
            vehicle.setBrand(rs.getString("brand"));
            vehicle.setModel(rs.getString("model"));
            vehicle.setPlateNumber(rs.getString("plate_number"));
            
            // 处理价格
            BigDecimal price = rs.getBigDecimal("price_per_minute");
            vehicle.setPricePerMinute(price != null ? price : BigDecimal.ZERO);
            
            vehicle.setStatus(rs.getInt("status"));
            
            // 获取经纬度
            Double longitude = rs.getDouble("longitude");
            Double latitude = rs.getDouble("latitude");
            
            if (!rs.wasNull()) {
                vehicle.setLongitude(longitude);
                vehicle.setLatitude(latitude);
                vehicle.setLocation(String.format("POINT(%f %f)", longitude, latitude));
                
                // 只在调试模式下输出日志
                if (System.getProperty("debug") != null) {
                    System.out.println(String.format(
                        "Vehicle %d location: longitude=%f, latitude=%f",
                        vehicle.getId(), longitude, latitude
                    ));
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Error mapping vehicle: " + e.getMessage());
            throw e;
        }
        return vehicle;
    }

    public boolean create(Vehicle vehicle) throws SQLException {
        String sql = "INSERT INTO vehicles (type, brand, model, plate_number, price_per_minute, location, status) " +
                    "VALUES (?, ?, ?, ?, ?, ST_PointFromText(?), ?)";
        
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, vehicle.getType());
            stmt.setString(2, vehicle.getBrand());
            stmt.setString(3, vehicle.getModel());
            stmt.setString(4, vehicle.getPlateNumber());
            
            // 处理价格
            BigDecimal price = vehicle.getPricePerMinute();
            if (price != null) {
                stmt.setBigDecimal(5, price);
            } else {
                stmt.setBigDecimal(5, BigDecimal.ZERO);
            }
            
            // 构建POINT字符串
            if (vehicle.getLongitude() != null && vehicle.getLatitude() != null) {
                String pointStr = String.format("POINT(%f %f)", 
                    vehicle.getLongitude(), vehicle.getLatitude());
                stmt.setString(6, pointStr);
            } else {
                stmt.setNull(6, Types.VARCHAR);
            }
            
            stmt.setInt(7, vehicle.getStatus());
            
            System.out.println("Executing create SQL with location: " + 
                (vehicle.getLongitude() != null ? 
                String.format("POINT(%f %f)", vehicle.getLongitude(), vehicle.getLatitude()) : "null") +
                ", Price: " + price);
            
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error in create: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    public boolean update(Vehicle vehicle) throws SQLException {
        String sql = "UPDATE vehicles SET type = ?, brand = ?, model = ?, " +
                    "plate_number = ?, price_per_minute = ?, location = ST_PointFromText(?), " +
                    "status = ? WHERE id = ?";
        
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            stmt.setString(1, vehicle.getType());
            stmt.setString(2, vehicle.getBrand());
            stmt.setString(3, vehicle.getModel());
            stmt.setString(4, vehicle.getPlateNumber());
            
            // 处理价格
            BigDecimal price = vehicle.getPricePerMinute();
            if (price != null) {
                stmt.setBigDecimal(5, price);
            } else {
                stmt.setBigDecimal(5, BigDecimal.ZERO);
            }
            
            // 构建POINT字符串
            if (vehicle.getLongitude() != null && vehicle.getLatitude() != null) {
                String pointStr = String.format("POINT(%f %f)", 
                    vehicle.getLongitude(), vehicle.getLatitude());
                stmt.setString(6, pointStr);
            } else {
                stmt.setNull(6, Types.VARCHAR);
            }
            
            stmt.setInt(7, vehicle.getStatus());
            stmt.setInt(8, vehicle.getId());
            
            System.out.println("Executing update SQL with location: " + 
                (vehicle.getLongitude() != null ? 
                String.format("POINT(%f %f)", vehicle.getLongitude(), vehicle.getLatitude()) : "null") +
                ", Price: " + price);
            
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error in update: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    public boolean delete(Integer id) throws SQLException {
        String sql = "DELETE FROM vehicles WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, id);
            return stmt.executeUpdate() > 0;
        }
    }
} 